package com.example.dao.daoImple;


import com.example.dao.ArticalDao;
import com.example.entity.Article;
import com.example.entity.Type;
import com.example.entity.vo.ArticleVo;
import com.example.entity.vo.SearchVO;
import com.example.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class ArticalDaoImpl implements ArticalDao {
    @Override
    public Article findArticleByArticalId(Long articalId) {
        String sql = "SELECT id,title,content,flag,update_time,comment_count," +
                " user_id,firstImg,likes FROM tb_article WHERE id = ?";
        List<Article> forList = JDBCUtil.getForList(Article.class, sql, articalId);
        return forList==null || forList.isEmpty() ?null:forList.get(0);
    }

    //查找用户热门文章
    @Override
    public List<ArticleVo> findArticleByUserId(Long userId) {
        String sql = "SELECT a.user_id,a.id,title,views,update_time,firstImg,u.nick_name,u.avatar,u.describe,TIMESTAMPDIFF(DAY,update_time,NOW()) differTime FROM tb_article a \n" +
                "LEFT JOIN tb_user u ON a.user_id = u.id \n" +
                " WHERE a.user_id  = (SELECT user_id FROM tb_article WHERE id = ?)\n" +
                " AND TIMESTAMPDIFF(DAY,update_time,NOW()) < 3 ORDER BY views DESC";
        return JDBCUtil.getForList(ArticleVo.class,sql,userId);
    }



//    @Override
//    public int differTime(Long articalId) {
//        String sql = "SELECT TIMESTAMPDIFF(DAY,create_time,NOW()) FROM tb_article WHERE id = ?";
//        Connection conn = null;
//        PreparedStatement psmt = null;
//        ResultSet rs = null;
//        try {
//            conn = JDBCUtil.getConn();
//            psmt = conn.prepareStatement(sql);
//            //把值放进去
//            psmt.setObject(1,articalId);
//            rs = psmt.executeQuery();
//            rs.next();
//            return rs.getInt(1);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }finally {
//            JDBCUtil.close(rs,psmt,conn);
//        }
//        return 0;
//    }

    @Override
    public List<Article> findArticalByBlur(String str) {
        String param = "%"+str+"%";
        String sql = "SELECT * FROM tb_article WHERE title LIKE ?";
        return JDBCUtil.getForList(Article.class,sql,str,param);
    }

    @Override
    public Article getArticleById(Long id) {
        String sql = "select id,title,content,flag,create_time,update_time,description,comment_count,user_id,firstImg,views,type_id,recommend,top,hot,likes,state from tb_article where id = ?";
        return JDBCUtil.getInstance(Article.class,sql,id);
    }

    @Override
    public int updateArticle(Article article) {
        String sql = "update tb_article set title=?,content=?,flag=?,update_time=?,description=?,comment_count=?,firstImg=?,views=?,type_id=?,state=? where id=?";
        return JDBCUtil.ALLCUD(sql,article.getTitle(),article.getContent(),article.getFlag(),article.getUpdateTime(),article.getDescription(),article.getCommentCount(),article.getFirstImg(),article.getViews(),article.getTypeId(),article.getState(),article.getId());
    }

    @Override
    public int addArticle(Article article) {
        String sql = "insert into tb_article(title,content,flag,create_time,update_time,description,user_id,firstImg,type_id) values(?,?,?,?,?,?,?,?,?)";
        return JDBCUtil.ALLCUD(sql,article.getTitle(),article.getContent(),article.getFlag(),article.getCreateTime(),article.getUpdateTime(),article.getDescription(),article.getUserId(),article.getFirstImg(),article.getTypeId());
    }


    @Override
    public int addLikes(Long userId,Long articleId) {
        String sql = "UPDATE tb_article a \n" +
                " LEFT JOIN tb_user u ON a.user_id = u.id \n" +
                " INNER JOIN tb_views v ON a.id = v.type_id AND u.id = a.user_id\n" +
                " SET a.likes = a.likes +1 , u.likes = u.likes + 1 ,v.is_like = 1\n" +
                " WHERE v.user_id = ? AND v.type_id = ?";
        return JDBCUtil.ALLCUD(sql,userId,articleId);
    }

    @Override
    public int reducedLikes(Long userId,Long articleId) {
        String sql = " UPDATE tb_article a  \n" +
                "LEFT JOIN tb_user u ON a.user_id = u.id \n" +
                "INNER JOIN tb_views v ON a.id = v.type_id AND u.id = a.user_id\n" +
                "SET a.likes = a.likes - 1 , u.likes = u.likes - 1 ,v.is_like = 0\n" +
                "WHERE v.user_id = ? AND v.type_id = ?";
        return JDBCUtil.ALLCUD(sql,userId,articleId);
    }

    @Override
    public int isCollect(Long userId, Long articleId) {
        String sql = "SELECT is_collect FROM tb_views WHERE user_id = ? AND type_id = ? and type = 0";
        return returnJudge(sql,userId,articleId);
    }

    @Override
    public List<Article> queryColletedByUserId(long userId) {
        String  sql = "SELECT *,0 as type from tb_article  where id in (SELECT type_id  from tb_views where user_id = ? and is_collect = 1);";
        List<Article> colletions = JDBCUtil.getForList(Article.class, sql, userId);
        return colletions ==null || colletions.isEmpty() ? null : colletions;
    }

    @Override
    public int updateColletionByUserIdArticleId(long userId, long articleId) {
        String  sql = "UPDATE tb_views SET is_collect = 0 where user_id = ? and type_id = ? and type = 0";
        return JDBCUtil.ALLCUD(sql, userId, articleId);

    }

    @Override
    public int addColletionByUserIdArticleId(long userId, long articleId) {
        String  sql = "UPDATE tb_views SET is_collect = 1 where user_id = ? and type_id = ? and type = 0";
        return JDBCUtil.ALLCUD(sql, userId, articleId);
    }

    @Override
    public int deleteById(long id) {
        String  sql = "DELETE  from tb_article where id = ?";
        return JDBCUtil.ALLCUD(sql,id);
    }

    @Override
    public List<Article> findAllArticleByUserId(Long userId) {
        String sql = "SELECT id,user_id,title,firstImg,views,update_time,0 as type FROM tb_article where user_id = ? and state = 0 ORDER BY update_time DESC";
        List<Article> articles = JDBCUtil.getForList(Article.class, sql, userId);

        return articles == null || articles.isEmpty() ? null : articles;
    }

    public int returnInt(String sql,Long articleId){
        Connection conn = null;
        PreparedStatement psmt = null;
        int rs = 0;
        try {
            conn = JDBCUtil.getConn();
            psmt = conn.prepareStatement(sql);
            //把值放进去
            psmt.setObject(1,articleId);
            rs = psmt.executeUpdate();
            return rs;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                psmt.close();
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return 0;
    }


    @Override
    public List<Article> queryHotArtical(long type) {
        String sql = "SELECT a.id,a.title,a.comment_count,a.create_time,a.firstImg,u.nick_name FROM tb_article a left join tb_user u on u.id = a.user_id WHERE a.type_id = ? ORDER BY a.create_time ASC limit 0,20";
        return JDBCUtil.getForList(Article.class,sql,type);
    }

    @Override
    public List<Type> queryType() {
        String sql = "SELECT id,name FROM tb_type";
        return JDBCUtil.getForList(Type.class,sql);
    }

    @Override
    public List<Article> queryHotTitle() {
        String sql = "SELECT id,title FROM tb_article WHERE 1=1 AND hot = 1 ORDER BY hot ASC limit 0,20";
        return JDBCUtil.getForList(Article.class,sql);
    }

    @Override
    public List<Article> SearchByKey(String key) {
        String param = "%"+key+"%";
        String sql = "SELECT a.id,a.title,u.nick_name,a.comment_count,a.update_time,a.firstImg, 0 as type \n" +
                "FROM tb_article a,tb_user u \n" +
                "WHERE 1=1 AND a.user_id = u.id AND a.title like ? \n" +
                "ORDER BY hot ASC \n" +
                "limit 0,10;\n";

        List<Article> articles = JDBCUtil.getForList(Article.class, sql, param);
        return articles == null || articles.isEmpty() ? null :articles;

    }
    @Override
    public List<Article> SearchByKeyByUserId(String key,Long userId) {
        String param = "%"+key+"%";
        String sql = "SELECT a.id,a.title,u.nick_name,a.comment_count,a.update_time,a.firstImg,a.views, 0 as type \n" +
                "FROM tb_article a,tb_user u \n" +
                "WHERE 1=1 AND u.id = ? AND a.user_id = u.id AND a.title like ? \n" +
                "ORDER BY hot ASC \n" +
                "limit 0,10;\n";

        List<Article> articles = JDBCUtil.getForList(Article.class, sql, userId,param);
        return articles == null || articles.isEmpty() ? null :articles;

    }

    @Override
    public int isLikes(Long userId, Long authordId) {
        String sql = "SELECT is_like FROM tb_views WHERE user_id = ? AND type_id = ? and type = 0";
        return returnJudge(sql,userId,authordId);
    }

    public int returnJudge(String sql,Long userId, Long authordId){
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtil.getConn();
            psmt = conn.prepareStatement(sql);
            //把值放进去
            psmt.setObject(1,userId);
            psmt.setObject(2,authordId);
            rs = psmt.executeQuery();
            while( rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(rs,psmt,conn);
        }
        return 0;
    }


}
